USE [influenza]
GO

/****** Object:  StoredProcedure [dbo].[SP_buscaPacienteLab]    Script Date: 03/04/2010 09:19:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
ALTER PROCEDURE [dbo].[SP_buscaPacienteLab]
@paterno varchar(30),
@materno varchar(30),
@nombre varchar(30),
@curp    varchar(30),
@cFolio  varchar(30),
@nUnidad varchar(3)
AS

BEGIN
declare @qry varchar(8000)


SET @qry='select catalogos.dbo.fUnidad_proced(a.id_sector,a.id_unidad) as Unidad, 
		  (a.cNombre+'' ''+a.cPaterno+'' ''+a.cMaterno) as Paciente, a.curp,a.nStatus,
		  b.fIngreso, a.id_principal as id_g,c.cFolio as cFolio ,b.nTipoPaciente as nTipoPaciente, 
		  d.id_registro as id_reg, a.id_unidad as iduni, a.id_sector as idse, 
		  a.nEntidad as idde, d.id_principal as id_lab,c.id_muestras
          from dat_generales a
          inner join dat_registro b on (a.id_principal = b.id_generales)
          inner join dat_laboratorio d on (a.id_principal = d.id_generales)
          inner join aux_muestras c on (b.id_principal = c.id_principal and d.nTipomuestra=c.nTipomuestra)
          Where '
      if @nUnidad<>33
        begin
			set @qry=@qry+'d.id_laboratorio = '+@nUnidad	
        end
      else
        begin
			set @qry=@qry+'b.id_principal > 0 '
        end
	if @paterno<>''
	  begin
		set @qry=@qry+'and cPaterno like ''%'+@paterno+'%'''
	  end	 
	if @materno<>''
	  begin
		set @qry=@qry+'and cMaterno like ''%'+@materno+'%'''
	  end
	if @nombre<>''
	  begin
		set @qry=@qry+'and cNombre like ''%'+@nombre+'%'''
	  end
	if @curp<>''
	  begin
		set @qry=@qry+'and curp like ''%'+@curp+'%'''
	  end
	if @cFolio<>''
	  begin
		set @qry=@qry+'and c.cFolio like ''%'+@cFolio+'%'''
	  end
	SET @qry=@qry+' group by a.cNombre,a.cPaterno,a.cMaterno,a.curp,a.nStatus,b.fIngreso,a.id_principal,
a.id_unidad,a.id_sector,b.nTipoPaciente,c.cFolio,b.id_principal,a.id_unidad,
a.id_sector,a.nEntidad,d.id_principal,d.id_registro,c.id_muestras'

exec(@qry)
END

GO

